﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for Tin
/// </summary>
public class Tin
{
    SqlCommand cmd;
    SqlDataReader dr;
	public Tin()
	{
		//
		// TODO: Add constructor logic here
		//
	}


    //lay tin menu ngang
    public List<ObjTin> getTinType(string type)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinGetTinByLoaiTinId", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Loai_tin_id", DbType.Int16);
        sqla[0].Value = type;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTin objTin;
        List<ObjTin> lst = new List<ObjTin>();
        while (dr.Read())
        {
            objTin = new ObjTin();
            objTin.Tieu_de = dr["tieu_de"].ToString();
            objTin.Tin_id = int.Parse(dr["tin_id"].ToString());
            objTin.Url = dr["url"].ToString();

            //loi thieu tom tat
            //da sua
            objTin.Tom_tat = dr["tom_tat"].ToString();
            lst.Add(objTin);
        }

        dr.Close();
        sqlConnection.Close();
        return lst;
    }


    public List<ObjTin> getAllTinType(string type)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinGetAllByLoaiTinId", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Loai_tin_id", DbType.Int16);
        sqla[0].Value = type;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTin objTin;
        List<ObjTin> lst = new List<ObjTin>();
        while (dr.Read())
        {
            objTin = new ObjTin();
            objTin.Tieu_de = dr["tieu_de"].ToString();
            objTin.Tin_id = int.Parse(dr["tin_id"].ToString());
            objTin.Url = dr["url"].ToString();
            objTin.Tin_cha_id = int.Parse(dr["tin_cha_id"].ToString());
            if(dr["loai_tin_id"].ToString()!="")
            objTin.Loai_tin_id = int.Parse(dr["loai_tin_id"].ToString());
            if(dr["vi_tri"].ToString()!="")
                objTin.Vi_tri=int.Parse(dr["vi_tri"].ToString());
            lst.Add(objTin);
        }

        dr.Close();
        sqlConnection.Close();
        return lst;
    }

    public List<ObjTin> getTinByTinCungLoai(string tin_cha_id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinGetByTinCungLoai", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Tin_id", DbType.Int32);
        

        sqla[0].Value = tin_cha_id;
        

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTin objTin;
        List<ObjTin> lst = new List<ObjTin>();
        while (dr.Read())
        {
            objTin = new ObjTin();
            objTin.Tieu_de = dr["tieu_de"].ToString();
            objTin.Tin_id = int.Parse(dr["tin_id"].ToString());
            objTin.Ngay_tao = dr["ngay_tao"].ToString();
            lst.Add(objTin);
        }

        dr.Close();
        sqlConnection.Close();
        return lst;
    }
    public List<ObjTin> getTinByTinChaId(string tin_cha_id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinGetByTinChaId", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Tin_cha_id", DbType.Int16);
        
        sqla[0].Value = tin_cha_id;
        
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTin objTin;
        List<ObjTin> lst = new List<ObjTin>();
        while (dr.Read())
        {
            objTin = new ObjTin();
            objTin.Tieu_de = dr["tieu_de"].ToString();
            objTin.Tin_id = int.Parse(dr["tin_id"].ToString());
            objTin.Anh_nho = dr["anh_nho"].ToString();
            objTin.Tom_tat = dr["tom_tat"].ToString();
            objTin.Ngay_tao = dr["ngay_tao"].ToString();
            objTin.Url = dr["url"].ToString();
            lst.Add(objTin);
        }

        dr.Close();
        sqlConnection.Close();
        return lst;
    }

    public ObjTin getTinByTinId(string tin_id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinGetById", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("@Tin_id", DbType.Int16);
        sqla[0].Value = tin_id;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        dr = cmd.ExecuteReader();
        ObjTin objTin = new ObjTin();
        
        if (dr.Read())
        {
            
            objTin.Tieu_de = dr["tieu_de"].ToString();
            objTin.Tin_id = int.Parse(dr["tin_id"].ToString());
            objTin.Url = dr["url"].ToString();
            objTin.Anh_to = dr["anh_to"].ToString();
            objTin.Tom_tat = dr["tom_tat"].ToString();
            objTin.Noi_dung = dr["noi_dung"].ToString();
            objTin.Anh_to = dr["anh_to"].ToString();
            objTin.Anh_nho = dr["anh_nho"].ToString();
            objTin.Vi_tri = int.Parse(dr["vi_tri"].ToString());
        }

        dr.Close();
        sqlConnection.Close();
        return objTin;
    }

    public void createTin(ObjTin objTin)
    {

        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinCreate", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[9];
        
        sqla[0] = new SqlParameter("@Tin_cha_id", DbType.Int16);
        sqla[1] = new SqlParameter("@Url", DbType.String);
        sqla[2] = new SqlParameter("@Tieu_de", DbType.String);
        sqla[3] = new SqlParameter("@Mo_ta_ngan", DbType.String);
        sqla[4] = new SqlParameter("@Noi_dung", DbType.String);
        sqla[5] = new SqlParameter("@Anh_to", DbType.String);
        sqla[6] = new SqlParameter("@Anh_nho", DbType.String);
        sqla[7] = new SqlParameter("@Vi_tri", DbType.Int16);
        sqla[8] = new SqlParameter("loai_tin_id", DbType.Int16);
        
        sqla[0].Value = objTin.Tin_cha_id;
        sqla[1].Value = objTin.Url;
        sqla[2].Value = objTin.Tieu_de;
        sqla[3].Value = objTin.Tom_tat;
        sqla[4].Value = objTin.Noi_dung;
        sqla[5].Value = objTin.Anh_to;
        sqla[6].Value = objTin.Anh_nho;
        sqla[7].Value = objTin.Vi_tri;
        sqla[8].Value = objTin.Loai_tin_id;
        
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void updateTin(ObjTin objTin)
    {

        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinUpdate", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[10];

        sqla[0] = new SqlParameter("@Tin_cha_id", DbType.Int16);
        sqla[1] = new SqlParameter("@Url", DbType.String);
        sqla[2] = new SqlParameter("@Tieu_de", DbType.String);
        sqla[3] = new SqlParameter("@Mo_ta_ngan", DbType.String);
        sqla[4] = new SqlParameter("@Noi_dung", DbType.String);
        sqla[5] = new SqlParameter("@Anh_to", DbType.String);
        sqla[6] = new SqlParameter("@Anh_nho", DbType.String);
        sqla[7] = new SqlParameter("@Vi_tri", DbType.Int16);
        sqla[8] = new SqlParameter("tin_id", DbType.Int16);
        sqla[9] = new SqlParameter("loai_tin_id", DbType.Int16);

        sqla[0].Value = objTin.Tin_cha_id;
        sqla[1].Value = objTin.Url;
        sqla[2].Value = objTin.Tieu_de;
        sqla[3].Value = objTin.Tom_tat;
        sqla[4].Value = objTin.Noi_dung;
        sqla[5].Value = objTin.Anh_to;
        sqla[6].Value = objTin.Anh_nho;
        sqla[7].Value = objTin.Vi_tri;
        sqla[8].Value = objTin.Tin_id;
        sqla[9].Value = objTin.Loai_tin_id;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void deleteTin(string id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinDelete", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("tin_id", DbType.Int32);
        sqla[0].Value = id;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }


    public void deleteTinByXml(string id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinDeleteByXml", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("Doc", DbType.Int32);
        sqla[0].Value = id;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

    public void updateTinVitriByXml(string id)
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(constr);

        sqlConnection.Open();
        cmd = new SqlCommand("sp_TinUpdateViTriByXml", sqlConnection);
        SqlParameter[] sqla = new SqlParameter[1];
        sqla[0] = new SqlParameter("Doc", DbType.Int32);
        sqla[0].Value = id;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(sqla);
        cmd.ExecuteNonQuery();
        sqlConnection.Close();
    }
}
